using System.Data;
using System.Data.OracleClient;
namespace WPFExcelReport
{
    public partial class VVVAVIS
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "TST":
                default:
                    #region
                    dwReturn["VVAMANDANT"] = app.Mandant;
                    dwReturn["VVAAVISNR"] = "00000000";
                    dwReturn["VVALO"] = "01";
                    dwReturn["VVAVERSDAT"] = "0";
                    dwReturn["VVAVERSART"] = "T  ";
                    dwReturn["VVASPED"] = "1  ";
                    dwReturn["VVATRANSDAT"] = "0";
                    dwReturn["VVATRANSUHR"] = "0";
                    dwReturn["VVATRANSBEN"] = "        ";
                    dwReturn["VVATRANSSTA"] = " ";
                    dwReturn["VVAVERSARTV"] = "   ";
                    dwReturn["VVASPEDV"] = "   ";
                    dwReturn["VVAVERSARTU"] = "   ";
                    dwReturn["VVASPEDU"] = "   ";
                    dwReturn["VVAFAHRDAT"] = "0";
                    dwReturn["VVAFAHRER"] = "                    ";
                    dwReturn["VVABFAHRER"] = "                    ";
                    dwReturn["VVAKFZ"] = "          ";
                    dwReturn["VVAAHAENGER"] = "          ";
                    dwReturn["VVALPLKW"] = "        ";
                    dwReturn["VVALG1"] = "0";
                    dwReturn["VVABR1"] = "0";
                    dwReturn["VVAHO1"] = "0";
                    dwReturn["VVALG2"] = "0";
                    dwReturn["VVABR2"] = "0";
                    dwReturn["VVAHO2"] = "0";
                    dwReturn["VVATIME"] = "0";
                    dwReturn["VVARMTYPE"] = " ";
                    dwReturn["VVAKZPLAN"] = " ";
                    dwReturn["VVASTATUS"] = " ";
                    dwReturn["VVAKZKOMP"] = " ";
                    dwReturn["VVAKZFBRIEF"] = " ";
                    dwReturn["VVAAM1"] = "  ";
                    dwReturn["VVAMG1"] = "0";
                    dwReturn["VVAAM2"] = "  ";
                    dwReturn["VVAMG2"] = "0";
                    dwReturn["VVAAM3"] = "  ";
                    dwReturn["VVAMG3"] = "0";
                    dwReturn["VVAAM4"] = "  ";
                    dwReturn["VVAMG4"] = "0";
                    dwReturn["VVAAM5"] = "  ";
                    dwReturn["VVAMG5"] = "0";
                    dwReturn["VVAAM6"] = "  ";
                    dwReturn["VVAMG6"] = "0";
                    dwReturn["VVAAM7"] = "  ";
                    dwReturn["VVAMG7"] = "0";
                    dwReturn["VVAAM8"] = "  ";
                    dwReturn["VVAMG8"] = "0";
                    dwReturn["VVALOVOR"] = "01";
                    dwReturn["VVALBVOR"] = "KO";
                    dwReturn["VVALPVOR"] = "        ";
                    dwReturn["VVAAUFBKZ"] = " ";
                    dwReturn["VVAGTBS"] = "00000";
                    dwReturn["VVAGTBK"] = "00000";
                    dwReturn["VVAKM1"] = "00000";
                    dwReturn["VVATARIF"] = "        ";
                    dwReturn["VVAORTKL"] = "  ";
                    dwReturn["VVAFRPFL"] = "0";
                    dwReturn["VVALAKL"] = "0";
                    dwReturn["VVAFRSATZ"] = "0";
                    dwReturn["VVAFRERRECH"] = "0";
                    dwReturn["VVAMARGEP"] = "0";
                    dwReturn["VVAMARGEA"] = "0";
                    dwReturn["VVASUMME"] = "0";
                    dwReturn["VVAVERSICH"] = "0";
                    dwReturn["VVANEBENKO"] = "0";
                    dwReturn["VVAWAEHRSLN"] = "   ";
                    dwReturn["VVAKOSTBGR"] = "0";
                    dwReturn["VVAFRNETTO"] = "0";
                    dwReturn["VVALIEFNR"] = "        ";
                    dwReturn["VVARENR"] = "          ";
                    dwReturn["VVAREPOS"] = "0000";
                    dwReturn["VVAREDAT"] = "0";
                    dwReturn["VVAWAEHRSL"] = "   ";
                    dwReturn["VVAHWAEHRSL"] = "   ";
                    dwReturn["VVAHEUROKURS"] = "0";
                    dwReturn["VVAHEUROKURSVK"] = " ";
                    dwReturn["VVAFEUROKURS"] = "0";
                    dwReturn["VVAFEUROKURSVK"] = " ";
                    dwReturn["VVASEQUNO"] = "000000000000000";
                    dwReturn["VVALFDPN"] = "0";
                    dwReturn["VVAVOLUMEN"] = "000000000";
                    dwReturn["VVAFILLER"] = "                                                                 ";
                    dwReturn["VVAAENZL"] = "0";
                    dwReturn["VVAAENB"] = "        ";
                    dwReturn["VVAAEND"] = "0";
                    dwReturn["VVANEUB"] = "KONGCH  ";
                    dwReturn["VVANEUD"] = "20141029"; 
                    #endregion
                    break;
                case "JLT":

                    break;
            }
        }
        public void InsertVVAVIS(DataRow dwData, string ITXDBVERSION = "50.24")
        { 
            dwData["VVANEUB"] = app.User;
            dwData["VVANEUD"] = date;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                default:
                case "YGR":
                    or1.CommandText = "INSERT INTO VVAVIS ( VVAMANDANT,VVAAVISNR,VVALO,VVAVERSDAT,VVAVERSART,VVASPED,VVATRANSDAT,VVATRANSUHR,VVATRANSBEN,VVATRANSSTA,VVAVERSARTV,VVASPEDV,VVAVERSARTU,VVASPEDU,VVAFAHRDAT,VVAFAHRER,VVABFAHRER,VVAKFZ,VVAAHAENGER,VVALPLKW,VVALG1,VVABR1,VVAHO1,VVALG2,VVABR2,VVAHO2,VVATIME,VVARMTYPE,VVAKZPLAN,VVASTATUS,VVAKZKOMP,VVAKZFBRIEF,VVAAM1,VVAMG1,VVAAM2,VVAMG2,VVAAM3,VVAMG3,VVAAM4,VVAMG4,VVAAM5,VVAMG5,VVAAM6,VVAMG6,VVAAM7,VVAMG7,VVAAM8,VVAMG8,VVALOVOR,VVALBVOR,VVALPVOR,VVAAUFBKZ,VVAGTBS,VVAGTBK,VVAKM1,VVATARIF,VVAORTKL,VVAFRPFL,VVALAKL,VVAFRSATZ,VVAFRERRECH,VVAMARGEP,VVAMARGEA,VVASUMME,VVAVERSICH,VVANEBENKO,VVAWAEHRSLN,VVAKOSTBGR,VVAFRNETTO,VVALIEFNR,VVARENR,VVAREPOS,VVAREDAT,VVAWAEHRSL,VVAHWAEHRSL,VVAHEUROKURS,VVAHEUROKURSVK,VVAFEUROKURS,VVAFEUROKURSVK,VVASEQUNO,VVALFDPN,VVAVOLUMEN,VVAFILLER,VVAAENZL,VVAAENB,VVAAEND,VVANEUB,VVANEUD)"
                    + " VALUES (:VVAMANDANT:,:VVAAVISNR:,:VVALO:,:VVAVERSDAT:,:VVAVERSART:,:VVASPED:,:VVATRANSDAT:,:VVATRANSUHR:,:VVATRANSBEN:,:VVATRANSSTA:,:VVAVERSARTV:,:VVASPEDV:,:VVAVERSARTU:,:VVASPEDU:,:VVAFAHRDAT:,:VVAFAHRER:,:VVABFAHRER:,:VVAKFZ:,:VVAAHAENGER:,:VVALPLKW:,:VVALG1:,:VVABR1:,:VVAHO1:,:VVALG2:,:VVABR2:,:VVAHO2:,:VVATIME:,:VVARMTYPE:,:VVAKZPLAN:,:VVASTATUS:,:VVAKZKOMP:,:VVAKZFBRIEF:,:VVAAM1:,:VVAMG1:,:VVAAM2:,:VVAMG2:,:VVAAM3:,:VVAMG3:,:VVAAM4:,:VVAMG4:,:VVAAM5:,:VVAMG5:,:VVAAM6:,:VVAMG6:,:VVAAM7:,:VVAMG7:,:VVAAM8:,:VVAMG8:,:VVALOVOR:,:VVALBVOR:,:VVALPVOR:,:VVAAUFBKZ:,:VVAGTBS:,:VVAGTBK:,:VVAKM1:,:VVATARIF:,:VVAORTKL:,:VVAFRPFL:,:VVALAKL:,:VVAFRSATZ:,:VVAFRERRECH:,:VVAMARGEP:,:VVAMARGEA:,:VVASUMME:,:VVAVERSICH:,:VVANEBENKO:,:VVAWAEHRSLN:,:VVAKOSTBGR:,:VVAFRNETTO:,:VVALIEFNR:,:VVARENR:,:VVAREPOS:,:VVAREDAT:,:VVAWAEHRSL:,:VVAHWAEHRSL:,:VVAHEUROKURS:,:VVAHEUROKURSVK:,:VVAFEUROKURS:,:VVAFEUROKURSVK:,:VVASEQUNO:,:VVALFDPN:,:VVAVOLUMEN:,:VVAFILLER:,:VVAAENZL:,:VVAAENB:,:VVAAEND:,:VVANEUB:,:VVANEUD:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }

        public DataSet QueryVVAVIS(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT P.* FROM VVAVIS P"
                         + " WHERE  VVAMANDANT=:VVAMANDANT: AND VVAAVISNR=:VVAAVISNR: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "SELECT DISTINCT VVLIEFSCH,TO_NUMBER(VVLIEFSCH) AS KEY,P.* FROM VVAVIS P"
                        + " JOIN VVLIEF V ON VVAMANDANT=VVMANDANT AND VVAAVISNR=VVAVISNR"
                       + " WHERE  VVAMANDANT=:VVAMANDANT: AND VVAAVISNR=:VVAAVISNR: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = "SELECT DISTINCT TO_NUMBER(VVAAVISNR) AS KEY, P.* FROM VVAVIS P"
                       + " WHERE  VVAMANDANT=:VVAMANDANT: AND VVAVERSART=:VVAVERSART:  AND VVASPED=:VVASPED: AND VVASTATUS=:VVASTATUS:"
                    +" ORDER BY VVAAVISNR DESC";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            SResult = Connect2Proxy(or1);

            SResult.Tables[0].TableName = TableName;

            return SResult;
        }

        public void DeleteVVAVIS(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM VVAVIS  "
                        + " WHERE  VVAMANDANT=:VVAMANDANT: AND VVAAVISNR=:VVAAVISNR: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public void UpdateVVAVIS(DataRow dwData, int option = 1)
        {
            if (dwData != null)
            {
                dwData["VVAAENB"] = app.User;
                dwData["VVAAEND"] = date;
            }
            if (option == 0)
            {
                switch (app.company)
                {
                    default:
                        option = 1;
                        break;
                }
            }
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE VVAVIS SET VVAMANDANT=:VVAMANDANT:,VVAAVISNR=:VVAAVISNR:,VVALO=:VVALO:,VVAVERSDAT=:VVAVERSDAT:,VVAVERSART=:VVAVERSART:,VVASPED=:VVASPED:,VVATRANSDAT=:VVATRANSDAT:,VVATRANSUHR=:VVATRANSUHR:,VVATRANSBEN=:VVATRANSBEN:,VVATRANSSTA=:VVATRANSSTA:,VVAVERSARTV=:VVAVERSARTV:,VVASPEDV=:VVASPEDV:,VVAVERSARTU=:VVAVERSARTU:,VVASPEDU=:VVASPEDU:,VVAFAHRDAT=:VVAFAHRDAT:,VVAFAHRER=:VVAFAHRER:,VVABFAHRER=:VVABFAHRER:,VVAKFZ=:VVAKFZ:,VVAAHAENGER=:VVAAHAENGER:,VVALPLKW=:VVALPLKW:,VVALG1=:VVALG1:,VVABR1=:VVABR1:,VVAHO1=:VVAHO1:,VVALG2=:VVALG2:,VVABR2=:VVABR2:,VVAHO2=:VVAHO2:,VVATIME=:VVATIME:,VVARMTYPE=:VVARMTYPE:,VVAKZPLAN=:VVAKZPLAN:,VVASTATUS=:VVASTATUS:,VVAKZKOMP=:VVAKZKOMP:,VVAKZFBRIEF=:VVAKZFBRIEF:,VVAAM1=:VVAAM1:,VVAMG1=:VVAMG1:,VVAAM2=:VVAAM2:,VVAMG2=:VVAMG2:,VVAAM3=:VVAAM3:,VVAMG3=:VVAMG3:,VVAAM4=:VVAAM4:,VVAMG4=:VVAMG4:,VVAAM5=:VVAAM5:,VVAMG5=:VVAMG5:,VVAAM6=:VVAAM6:,VVAMG6=:VVAMG6:,VVAAM7=:VVAAM7:,VVAMG7=:VVAMG7:,VVAAM8=:VVAAM8:,VVAMG8=:VVAMG8:,VVALOVOR=:VVALOVOR:,VVALBVOR=:VVALBVOR:,VVALPVOR=:VVALPVOR:,VVAAUFBKZ=:VVAAUFBKZ:,VVAGTBS=:VVAGTBS:,VVAGTBK=:VVAGTBK:,VVAKM1=:VVAKM1:,VVATARIF=:VVATARIF:,VVAORTKL=:VVAORTKL:,VVAFRPFL=:VVAFRPFL:,VVALAKL=:VVALAKL:,VVAFRSATZ=:VVAFRSATZ:,VVAFRERRECH=:VVAFRERRECH:,VVAMARGEP=:VVAMARGEP:,VVAMARGEA=:VVAMARGEA:,VVASUMME=:VVASUMME:,VVAVERSICH=:VVAVERSICH:,VVANEBENKO=:VVANEBENKO:,VVAWAEHRSLN=:VVAWAEHRSLN:,VVAKOSTBGR=:VVAKOSTBGR:,VVAFRNETTO=:VVAFRNETTO:,VVALIEFNR=:VVALIEFNR:,VVARENR=:VVARENR:,VVAREPOS=:VVAREPOS:,VVAREDAT=:VVAREDAT:,VVAWAEHRSL=:VVAWAEHRSL:,VVAHWAEHRSL=:VVAHWAEHRSL:,VVAHEUROKURS=:VVAHEUROKURS:,VVAHEUROKURSVK=:VVAHEUROKURSVK:,VVAFEUROKURS=:VVAFEUROKURS:,VVAFEUROKURSVK=:VVAFEUROKURSVK:,VVASEQUNO=:VVASEQUNO:,VVALFDPN=:VVALFDPN:,VVAVOLUMEN=:VVAVOLUMEN:,VVAFILLER=:VVAFILLER:,VVAAENZL=:VVAAENZL:,VVAAENB=:VVAAENB:,VVAAEND=:VVAAEND:,VVANEUB=:VVANEUB:,VVANEUD=:VVANEUD:"
                        + " WHERE  VVAMANDANT=:VVAMANDANT: AND VVAAVISNR=:VVAAVISNR: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 5:
                    or1.CommandText = "UPDATE VVLIEF SET VVAVISNR=:VVAAVISNR:"
                        + " WHERE VVMANDANT=:VVAMANDANT: AND VVLIEFSCH=:VVAFILLER:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

    }
}
